SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE VIEW dbo.vw_ordendeMerito_Propuestas
AS
SELECT DISTINCT 
                      SUBSTRING(CO.Descripcion, 1, 200) AS Convocatoria, N.NivelEnsenanza AS Nivel, IC.Modalidad, m.Descripcion, CL.Descripcion AS Ciclo, 
                      IC.Ciclo AS IdCiclo, L.localidad, L.idLocalidad, CG.idCargoGenerico AS IdCargo, CG.Descripcion AS Cargo, AG.idAsignaturaGenerica AS IdAsignatura, 
                      SUBSTRING(AG.Descripcion, 9, 150) AS Asignatura, C.Descripcion AS CategoriaDeTitulo, A.Documento, A.Nombres AS NombreYApellido, 
                      ROUND(IC.Puntaje, 5) AS Puntaje, A.Domicilio AS Direccion, L2.localidad AS ALocalidad, A.Telefono AS TE, A.LegajoDeMedia AS Legajo, 
                      IC.PromedioTitulo AS Promedio, IC.TituloEspecifico, IC.SituacionRevista, CASE WHEN N.IdNivelEnsenanza IN (2) 
                      THEN 1 WHEN N.IdNivelEnsenanza IN (4) THEN 2 WHEN N.IdNivelEnsenanza = 3 AND CL.IdCiclo = 1 THEN 1 WHEN N.IdNivelEnsenanza = 3 AND 
                      CL.IdCiclo = 2 THEN 2 ELSE 3 END AS TituloDelReporte, IC.TituloValorado, 
                      dbo.tb_Organizaciones.Nombre + ' - ' + tb_Localidades_1.localidad AS EscuelaDeOrigen, 
                      dbo.tb_CalificacionesDeInscripcion.Puntaje AS pje_servicios_Gral, tb_CalificacionesDeInscripcion_1.Puntaje AS pje_Servicios_Nives, 
                      tb_CalificacionesDeInscripcion_2.Puntaje AS pje_servicios_Cargo, CO.idConvocatoria, IC.Nivel AS IdNivel
FROM         dbo.tb_CalificacionesDeInscripcion INNER JOIN
                      dbo.tb_InscripcionesConvocatorias IC INNER JOIN
                      dbo.tb_Localidades L ON IC.Localidad = L.idLocalidad INNER JOIN
                      dbo.tb_CargosGenericos CG ON CG.idCargoGenerico = IC.CargoGenerico INNER JOIN
                      dbo.tb_AsignaturasGenericas AG ON AG.idAsignaturaGenerica = IC.AsignaturaGenerica INNER JOIN
                      dbo.tb_Agentes A ON A.idAgente = IC.Agente INNER JOIN
                      dbo.tb_Convocatorias CO ON CO.idConvocatoria = IC.Convocatoria INNER JOIN
                      dbo.tb_NivelesEnsenanza N ON N.idNivelEnsenanza = IC.Nivel INNER JOIN
                      dbo.tb_ModalidadesEnsenanza m ON m.idModalidad = IC.Modalidad INNER JOIN
                      dbo.tb_Ciclos CL ON CL.IdCiclo = IC.Ciclo ON dbo.tb_CalificacionesDeInscripcion.Inscripcion = IC.idInscripcionConvocatoria INNER JOIN
                      dbo.tb_CalificacionesDeInscripcion tb_CalificacionesDeInscripcion_1 ON 
                      IC.idInscripcionConvocatoria = tb_CalificacionesDeInscripcion_1.Inscripcion INNER JOIN
                      dbo.tb_CalificacionesDeInscripcion tb_CalificacionesDeInscripcion_2 ON 
                      IC.idInscripcionConvocatoria = tb_CalificacionesDeInscripcion_2.Inscripcion LEFT OUTER JOIN
                      dbo.tb_CategoriasDeTitulo C ON C.idCategoria = IC.CategoriaDeTitulo LEFT OUTER JOIN
                      dbo.tb_Localidades L2 ON A.Localidad = L2.idLocalidad LEFT OUTER JOIN
                      dbo.tb_Organizaciones INNER JOIN
                      dbo.tb_Localidades tb_Localidades_1 ON dbo.tb_Organizaciones.Localidad = tb_Localidades_1.idLocalidad ON 
                      IC.Organizacion = dbo.tb_Organizaciones.idOrganizaciones
WHERE     (CO.TipoConvocatoria = 11) AND (dbo.tb_CalificacionesDeInscripcion.Concepto = 650) AND (tb_CalificacionesDeInscripcion_1.Concepto = 651) AND 
                      (tb_CalificacionesDeInscripcion_2.Concepto = 652)
GO
EXEC sp_addextendedproperty N'MS_DiagramPane1', N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[42] 4[16] 3[21] 2) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1[50] 2[25] 3) )"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4[30] 2[25] 3) )"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2[66] 3) )"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1 [56] 4 [18] 2))"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = -192
         Left = 0
      End
      Begin Tables = 
         Begin Table = "tb_CalificacionesDeInscripcion"
            Begin Extent = 
               Top = 0
               Left = 770
               Bottom = 115
               Right = 963
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "IC"
            Begin Extent = 
               Top = 21
               Left = 579
               Bottom = 442
               Right = 783
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L"
            Begin Extent = 
               Top = 111
               Left = 273
               Bottom = 226
               Right = 463
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CG"
            Begin Extent = 
               Top = 89
               Left = 1121
               Bottom = 204
               Right = 1311
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "AG"
            Begin Extent = 
               Top = 197
               Left = 1321
               Bottom = 312
               Right = 1511
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "A"
            Begin Extent = 
               Top = 0
               Left = 269
               Bottom = 115
               Right = 471
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CO"
            Begin Extent = 
               Top = 16
               Left = 1358
               Bottom = 131
               Right = 1548
            End
            DisplayFlags = 280
', 'USER', N'dbo', 'VIEW', N'vw_ordendeMerito_Propuestas', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane2', N'            TopColumn = 0
         End
         Begin Table = "N"
            Begin Extent = 
               Top = 137
               Left = 0
               Bottom = 276
               Right = 192
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "m"
            Begin Extent = 
               Top = 361
               Left = 0
               Bottom = 461
               Right = 190
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "CL"
            Begin Extent = 
               Top = 299
               Left = 217
               Bottom = 384
               Right = 407
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_CalificacionesDeInscripcion_1"
            Begin Extent = 
               Top = 0
               Left = 1105
               Bottom = 115
               Right = 1298
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_CalificacionesDeInscripcion_2"
            Begin Extent = 
               Top = 81
               Left = 915
               Bottom = 196
               Right = 1108
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "C"
            Begin Extent = 
               Top = 328
               Left = 825
               Bottom = 443
               Right = 1015
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "L2"
            Begin Extent = 
               Top = 59
               Left = 20
               Bottom = 174
               Right = 210
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_Organizaciones"
            Begin Extent = 
               Top = 259
               Left = 1069
               Bottom = 374
               Right = 1259
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "tb_Localidades_1"
            Begin Extent = 
               Top = 311
               Left = 1425
               Bottom = 426
               Right = 1615
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      RowHeights = 220
      Begin ColumnWidths = 32
         Width = 284
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
         Width = 1440
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1815
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
      End
   ', 'USER', N'dbo', 'VIEW', N'vw_ordendeMerito_Propuestas', NULL, NULL
GO
EXEC sp_addextendedproperty N'MS_DiagramPane3', N'End
End
', 'USER', N'dbo', 'VIEW', N'vw_ordendeMerito_Propuestas', NULL, NULL
GO
DECLARE @xp int
SELECT @xp=3
EXEC sp_addextendedproperty N'MS_DiagramPaneCount', @xp, 'USER', N'dbo', 'VIEW', N'vw_ordendeMerito_Propuestas', NULL, NULL
GO
